Release 10.1A: OpenEdge Data Management:
SQL Reference

CASE

Specifies a series of search conditions and associated result expressions. The general form is called a searched case expression. SQL returns the value specified by the first result expression whose associated search condition evaluates as true. If none of the search conditions evaluates as true, the CASE expression returns a NULL value, or the value of some other default expression if the CASE expression includes the ELSE clause.

CASE also supports syntax for a shorthand notation, called a simple case expression, for evaluating whether one expression is equal to a series of other expressions.

Syntax

searched_case_expr | simple_case_expr

searched_case_expr

Syntax

CASE WHEN search_condition THEN { result_expr | NULL } [ ... ] [ ELSE expr | NULL ] END

simple_case_expr

Syntax

CASE primary_expr WHEN expr THEN { result_expr | NULL } [ ... ] [ ELSE expr | NULL ]

CASE

Specifies a searched case expression. It must be followed by one or more WHEN-THEN clauses, each specifying a search condition and corresponding expression.

WHEN search_condition THEN { result_expr | NULL }

Specifies a search condition and corresponding expression. SQL evaluates search_condition. If search_condition evaluates as true, CASE returns the value specified by result_expr, or NULL, if the clause specifies THEN NULL.

If search_condition evaluates as false, SQL evaluates the next WHEN-THEN clause, if any, or the ELSE clause, if it is specified.

CASE primary_expr

Specifies a simple case expression. In a simple case expression, one or more WHEN-THEN clauses specify two expressions.

WHEN expr THEN { result_expr | NULL }

Prompts SQL to evaluate expr and compare it with primary_expr specified in the CASE clause. If they are equal, CASE returns the value specified by result_expr (or NULL, if the clause specifies THEN NULL).

If expr is not equal to primary_expr, SQL evaluates the next WHEN-THEN clause, if any, or the ELSE clause, if it is specified.

ELSE { expr | NULL }

Specifies an optional expression whose value SQL returns if none of the conditions specified in WHEN-THEN clauses are satisfied. If the CASE expression omits the ELSE clause, it is the same as specifying ELSE NULL.

Examples

A simple case expression can always be expressed as a searched case expression. This example illustrates a simple case expression:

CASE primary_expr WHEN expr1 THEN result_expr1 WHEN expr2 THEN result_expr2 ELSE expr3 END

The simple case expression in the preceding CASE example is equivalent to the following searched case expression:

CASE WHEN primary_expr = expr1 THEN result_expr1 WHEN primary_expr = expr2 THEN result_expr2 ELSE expr3 END

The following example shows a searched case expression that assigns a label denoting suppliers as 'In Mass' if the state column value is 'MA':

select lastname, city, case when state = 'MA' then 'In Mass' else 'Not in Mass' end from supplier; Lastname City searched_case(State,MA,In Mass,) ---------------- ---------------- -------------------------------- GolfWorld Suppl Boston In Mass Pool Swimming S Valkeala Not in Mass Nordic Ski Whol Hingham In Mass Champion Soccer Harrow Not in Mass ABC Sports Supp Boston In Mass Seasonal Sports Bedford In Mass Tennis Supplies Boston In Mass Boating Supplie Jacksonville Not in Mass Aerobic Supplie Newport Beach Not in Mass Sports Unlimite Irving Not in Mass

The following example shows the equivalent simple case expression:

SELECT lastname, city case state when 'MA' then 'In Mass' else 'Not in Mass' end FROM supplier; Lastname City simple_case(State,MA,In Mass,) ---------------- ---------------- ------------------------------- GolfWorld Suppl Boston In Mass Pool Swimming S Valkeala Not in Mass Nordic Ski Whol Hingham In Mass Champion Soccer Harrow Not in Mass ABC Sports Supp Boston In Mass Seasonal Sports Bedford In Mass Tennis Supplies Boston In Mass Boating Supplie Jacksonville Not in Mass Aerobic Supplie Newport Beach Not in Mass Sports Unlimite Irving Not in Mass

Notes

This function is not allowed in a GROUP BY clause.
Arguments to this function cannot be query expressions.

Compatibility

SQL compatible